{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TODO\n",
    "\n",
    "```py\n",
    "res_df = await self.pdb_ob.fetch_from_web_api('api/pdb/entry/residue_listing/', PDB.to_dataframe)\n",
    "res_dict = res_df[res_df.observed_ratio.gt(0)].iloc[0].to_dict() # TODO: make it more efficient\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "from typing import Callable, NamedTuple, Optional\n",
    "from pandas import read_csv, DataFrame\n",
    "from dataframe_sql import register_temp_table, query\n",
    "from pandasql import sqldf\n",
    "pysqldf = lambda q: sqldf(q, globals())\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 32.9 ms\nWall time: 6.98 ms\n"
    }
   ],
   "source": [
    "workdir = Path(r'C:\\GitWorks\\pdb-profiling\\test\\demo\\api\\pdb\\entry\\residue_listing')\n",
    "%time demo_df_1 = read_csv(workdir/r'api%pdb%entry%residue_listing%+5tzs.tsv', sep='\\t')\n",
    "%time demo_df_2 = read_csv(workdir/r'api%pdb%entry%residue_listing%+2on9.tsv', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 17 ms\nWall time: 1.99 ms\n"
    }
   ],
   "source": [
    "%time register_temp_table(demo_df_1, \"demo_df_1\")\n",
    "%time register_temp_table(demo_df_2, \"demo_df_2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 8.05 s\nWall time: 11 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'author_insertion_code': nan,\n 'author_residue_number': 1,\n 'multiple_conformers': nan,\n 'observed_ratio': 1,\n 'residue_name': 'VAL',\n 'residue_number': 1,\n 'chain_id': 'A',\n 'struct_asym_id': 'A',\n 'entity_id': 1,\n 'pdb_id': '2on9'}"
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "source": [
    "%time query(\"\"\"SELECT * FROM demo_df_1 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]\n",
    "%time query(\"\"\"SELECT * FROM demo_df_2 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 16 ms\nWall time: 15 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'author_insertion_code': nan,\n 'author_residue_number': 1,\n 'multiple_conformers': nan,\n 'observed_ratio': 1,\n 'residue_name': 'VAL',\n 'residue_number': 1,\n 'chain_id': 'A',\n 'struct_asym_id': 'A',\n 'entity_id': 1,\n 'pdb_id': '2on9'}"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "%time query(\"\"\"SELECT * FROM demo_df_1 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]\n",
    "%time query(\"\"\"SELECT * FROM demo_df_2 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 18.9 ms\nWall time: 17 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'struct_asym_id': 'A', 'residue_number': 1, 'observed_ratio': 1}"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "%time query(\"\"\"SELECT struct_asym_id,residue_number,observed_ratio FROM demo_df_1 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]\n",
    "%time query(\"\"\"SELECT struct_asym_id,residue_number,observed_ratio FROM demo_df_2 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 736 ms\nWall time: 21.9 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'author_insertion_code': None,\n 'author_residue_number': 1,\n 'multiple_conformers': None,\n 'observed_ratio': 1,\n 'residue_name': 'VAL',\n 'residue_number': 1,\n 'chain_id': 'A',\n 'struct_asym_id': 'A',\n 'entity_id': 1,\n 'pdb_id': '2on9'}"
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "%time pysqldf(\"\"\"SELECT * FROM demo_df_1 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]\n",
    "%time pysqldf(\"\"\"SELECT * FROM demo_df_2 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 1.22 s\nWall time: 19.9 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'struct_asym_id': 'A', 'residue_number': 1}"
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "%time pysqldf(\"\"\"SELECT struct_asym_id,residue_number FROM demo_df_1 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]\n",
    "%time pysqldf(\"\"\"SELECT struct_asym_id,residue_number FROM demo_df_2 WHERE observed_ratio > 0 LIMIT 1\"\"\").to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 5.99 ms\nWall time: 5.98 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'author_insertion_code': nan,\n 'author_residue_number': 5,\n 'multiple_conformers': nan,\n 'observed_ratio': 1.0,\n 'residue_name': 'N',\n 'residue_number': 4,\n 'chain_id': '0',\n 'struct_asym_id': 'A',\n 'entity_id': 1,\n 'pdb_id': '5tzs'}"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "%time demo_df_1[demo_df_1.observed_ratio.gt(0)].iloc[0].to_dict()\n",
    "%time demo_df_1[demo_df_1.observed_ratio.gt(0)].head(1).to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 8.98 ms\nWall time: 5.98 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'struct_asym_id': 'A', 'residue_number': 4}"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "%time demo_df_1[demo_df_1.observed_ratio.gt(0)][['struct_asym_id','residue_number']].iloc[0].to_dict()\n",
    "%time demo_df_1[demo_df_1.observed_ratio.gt(0)][['struct_asym_id','residue_number']].head(1).to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 1.99 ms\nWall time: 2.99 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'author_insertion_code': nan,\n 'author_residue_number': 1,\n 'multiple_conformers': nan,\n 'observed_ratio': 1,\n 'residue_name': 'VAL',\n 'residue_number': 1,\n 'chain_id': 'A',\n 'struct_asym_id': 'A',\n 'entity_id': 1,\n 'pdb_id': '2on9'}"
     },
     "metadata": {},
     "execution_count": 73
    }
   ],
   "source": [
    "%time demo_df_2[demo_df_2.observed_ratio.gt(0)].iloc[0].to_dict()\n",
    "%time demo_df_2[demo_df_2.observed_ratio.gt(0)].head(1).to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 4.99 ms\nWall time: 3.99 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'struct_asym_id': 'A', 'residue_number': 1}"
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "%time demo_df_2[demo_df_2.observed_ratio.gt(0)][['struct_asym_id','residue_number']].iloc[0].to_dict()\n",
    "%time demo_df_2[demo_df_2.observed_ratio.gt(0)][['struct_asym_id','residue_number']].head(1).to_dict('records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 262 ms\nWall time: 13 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "{'author_insertion_code': nan,\n 'author_residue_number': 1,\n 'multiple_conformers': nan,\n 'observed_ratio': 1,\n 'residue_name': 'VAL',\n 'residue_number': 1,\n 'chain_id': 'A',\n 'struct_asym_id': 'A',\n 'entity_id': 1,\n 'pdb_id': '2on9'}"
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "%time demo_df_1.query('observed_ratio > 0').iloc[0].to_dict()\n",
    "%time demo_df_2.query('observed_ratio > 0').iloc[0].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def pd_iter_func(df: DataFrame, criteria: Callable[[NamedTuple], bool]) -> Optional[NamedTuple]:\n",
    "    for row in df.itertuples(index=False):\n",
    "        if criteria(row):\n",
    "            return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "criteria_func = lambda row: row.observed_ratio > 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Wall time: 2.99 ms\nWall time: 1.99 ms\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "Pandas(author_insertion_code=nan, author_residue_number=1, multiple_conformers=nan, observed_ratio=1, residue_name='VAL', residue_number=1, chain_id='A', struct_asym_id='A', entity_id=1, pdb_id='2on9')"
     },
     "metadata": {},
     "execution_count": 125
    }
   ],
   "source": [
    "%time pd_iter_func(demo_df_1, criteria_func)\n",
    "%time pd_iter_func(demo_df_2, criteria_func)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}